CREATE PROCEDURE sp_RegisterCourse(@studentId VARCHAR(5), @courseId VARCHAR(8), @building VARCHAR(15), @roomNumber VARCHAR(7), @semester VARCHAR(6), @year NUMERIC(4, 0), @timeSlotId VARCHAR(4))
AS
BEGIN
	BEGIN TRANSACTION RegisterCourse;
	DECLARE @capacity AS NUMERIC(4, 0);
	SELECT @capacity = capacity FROM classroom WHERE building = @building AND room_number = @roomNumber;
	DECLARE @sectionId AS VARCHAR(8);
	SELECT @sectionId = sec_id FROM section 
	WHERE semester = @semester AND year = @year AND time_slot_id = @timeSlotId AND building = @building AND room_number = @roomNumber;
	IF ((SELECT DISTINCT COUNT(ID) FROM takes WHERE course_id = @courseId AND sec_id = @sectionId AND semester = @semester AND year = @year) < @capacity)
		BEGIN
		INSERT INTO takes(ID, course_id, sec_id, semester, year) VALUES(@studentId, @courseId, @sectionId, @semester, @year);
		COMMIT TRANSACTION;
		END;
	ELSE ROLLBACK TRANSACTION RegisterCourse;
END;